TRUNCATE item_behaivor_history_month;

INSERT INTO item_behaivor_history_month 
    (item_id, profit, inversion, speed, friction, roi, perishable, last_update)
WITH VentasMensuales AS (
    -- obtenemos las ventas por mes
    SELECT 
        pvi.item_id,
        svi.purchase_vs_itemId,
        DATE_FORMAT(svi.date_registered, '%Y-%m-01') as mes,
        SUM((svi.qty - svi.returned_qty) * svi.price) as ingresos_totales,
        SUM(svi.qty - svi.returned_qty) as unidades_vendidas,
        COUNT(DISTINCT svi.saleId) as transacciones_ventas,
        MAX(svi.date_registered) as fecha_ultima_venta,
		SUM(
		    (svi.qty - svi.returned_qty) * (
		        pvi.unit_price + 
		        CASE 
		            WHEN (pvi.qty - pvi.returned_qty) = 0 THEN 0
		            ELSE (pvi.cogs_row_amount / (pvi.qty - pvi.returned_qty))
		        END
		    )
		) AS costo_total_vendido
    FROM sale_vs_item svi
    inner join sale s on s.id = svi.saleId and  s.deleted = 0
    INNER JOIN purchase_vs_item pvi ON svi.purchase_vs_itemId = pvi.id
    inner join purchase p on p.id = pvi.purchaseId and  p.deleted = 0
    WHERE svi.deleted = 0
    GROUP BY pvi.item_id, svi.purchase_vs_itemId, DATE_FORMAT(svi.date_registered, '%Y-%m-01')
    
),
ComprasMensuales AS (
    -- obtenemos compras por mes
    SELECT 
        purchase_vs_item.id as purchase_item_id,
        purchase_vs_item.item_id,
		purchase_vs_item.qty as unidades_compradas,
        purchase_vs_item.date_registered as fecha_compra,
        purchase_vs_item.total_price as inversion_item
    FROM purchase_vs_item
    inner join purchase on purchase.id = purchase_vs_item.purchaseId and  purchase_vs_item.deleted = 0
    WHERE purchase_vs_item.deleted = 0
),
PerdidasMensuales AS (
    -- calculo de perdidas
    SELECT 
        purchase_vs_lost.purchase_vs_itemId,
        SUM(purchase_vs_lost.lost_qty) as total_lost_qty
    FROM purchase_vs_lost
    inner join purchase_vs_item on purchase_vs_item.id = purchase_vs_lost.purchase_vs_itemId and purchase_vs_item.deleted = 0
    inner join purchase on purchase.id = purchase_vs_item.purchaseId and  purchase_vs_item.deleted = 0
    WHERE purchase_vs_lost.deleted = 0 and purchase_vs_lost.return_item = ''
    GROUP BY purchase_vs_lost.purchase_vs_itemId

)
-- cosolidacion y insercion de datos
SELECT 
    v.item_id AS item_name,
    SUM(v.ingresos_totales - v.costo_total_vendido) AS profit,
    SUM(c.inversion_item) AS inversion,
    AVG(DATEDIFF(v.fecha_ultima_venta, c.fecha_compra)) AS speed_days,
    AVG(v.transacciones_ventas) AS friction_transactions,
    CASE 
        WHEN SUM(c.inversion_item) > 0 
        THEN (SUM(v.ingresos_totales - v.costo_total_vendido) / SUM(c.inversion_item)) * 100 
        ELSE 0 
    END AS roi,
    CASE 
        WHEN SUM(c.unidades_compradas) > 0 
        THEN SUM(COALESCE(l.total_lost_qty, 0)) / SUM(c.unidades_compradas)
        ELSE 0 
    END AS perishable_rate,
    LAST_DAY(v.mes) AS last_update
FROM VentasMensuales v
INNER JOIN ComprasMensuales c ON v.purchase_vs_itemId = c.purchase_item_id
LEFT JOIN PerdidasMensuales l ON c.purchase_item_id = l.purchase_vs_itemId
GROUP BY v.item_id, v.mes;


